package com.roc.web.action.merinfo;

import java.util.ArrayList;
import java.util.List;

import com.roc.page.BaseSearchCondition;
import com.roc.page.WhereHQLFieldBean;
import com.roc.util.Constance;
import com.roc.util.constant.Role;

public class MerinfoysbSearchCondition extends BaseSearchCondition {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	
	private String roleType; //角色
	private String oprId; //操作员id
	private String supRoleType;	//上级角色
	private String supOprId; //上级id
	private String mchtCd; //商户号
	private String mchtName; //商户简称
	private String orgCode; //所属机构
	private String status; //状态
	private String directMerFlag; //直属商户标识

	private String cardNo; //身份证
	private String rsvl3; //结算账号
	private String perName; //联系人
	private String perPhone; //手机号码
	private String minDate;//入网时间
	private String maxDate;//入网时间

	@Override
	protected List<WhereHQLFieldBean> getWhereHQLFieldBeanList() {
		List<WhereHQLFieldBean> filedBeanList = new ArrayList<WhereHQLFieldBean>();
		if(Constance.isNotEmpty(mchtCd)){
			filedBeanList.add(new WhereHQLFieldBean("mi.mcht_cd", mchtCd.trim(), "=" , false));
		}
		if(Constance.isNotEmpty(mchtName)){
			filedBeanList.add(new WhereHQLFieldBean("mi.name_busi", mchtName.trim(), "=", false));
		}
		if(Constance.isNotEmpty(status) && !status.trim().equals("99")){ //状态
			filedBeanList.add(new WhereHQLFieldBean("mi.status", status.trim(), "=", false));
		}
		if(Constance.isNotEmpty(cardNo)){
			filedBeanList.add(new WhereHQLFieldBean("mi.certif_no", cardNo.trim(), "=" , false));
		}
		if(Constance.isNotEmpty(rsvl3)){
			filedBeanList.add(new WhereHQLFieldBean("b.account", rsvl3.trim(), "=" , false));
		}
		if(Constance.isNotEmpty(perName)){
			filedBeanList.add(new WhereHQLFieldBean("mi.contact_name", perName.trim(), "=" , false));
		}
		if(Constance.isNotEmpty(perPhone)){
			filedBeanList.add(new WhereHQLFieldBean("mi.contact_phoneno", perPhone.trim(), "=" , false));
		}
		if(Constance.isNotEmpty(minDate)){ //入网时间
			filedBeanList.add(new WhereHQLFieldBean("mi.appl_date", minDate.trim(),">=", false));
		}
		if(Constance.isNotEmpty(maxDate)){ //入网时间
			filedBeanList.add(new WhereHQLFieldBean("mi.appl_date", maxDate.trim(),"<=", false));
		}
		return filedBeanList;
	}

	@Override
	protected String getRowCountHQL() {
		StringBuffer sb = new StringBuffer();
		sb.append("select count(1) from tbl_mcht_inf mi, tbl_mcht_bankaccount b ,tbl_term_inf ti ");
		if(Role.O_MANAGER.getCode().equals(supRoleType) || Role.AGENT.getCode().equals(supRoleType)){
			sb.append(",(select a.serial_num,a.pos_brand from t_pos_agent a,");
			sb.append("(select to_char(t.id) tid from t_operators t start with id = ").append(supOprId);
			sb.append(" connect by prior t.id = t.supri_id and t.role_type not in ('15', '16', '17')) tt where a.agent_id = tt.tid group by a.serial_num,a.pos_brand) tem ");
		}
		if(Role.MARKET_DIRECT.getCode().equals(roleType) || Role.MARKET_LEAD.getCode().equals(roleType)){
			sb.append(",(select a.serial_num,a.pos_brand from t_pos_agent a,(select m.id tid  from t_operators m where m.role_type in ('02', '03') ");
			sb.append(" and m.oneagentid in (select agentid from t_oper_role t where t.operid = ").append(oprId).append(")) tt where a.agent_id = tt.tid group by a.serial_num,a.pos_brand) tem ");
		}
		sb.append(" where mi.group_cd='3' and mi.mcht_cd = b.owner_cd and mi.mcht_cd = ti.mcht_cd ");
		if(Role.O_MANAGER.getCode().equals(supRoleType) || Role.AGENT.getCode().equals(supRoleType)
				|| Role.MARKET_DIRECT.getCode().equals(roleType) || Role.MARKET_LEAD.getCode().equals(roleType)){
			sb.append(" and ti.device_cd = tem.serial_num and ti.tmnl_brand = tem.pos_brand");
		}
		if(Constance.isNotEmpty(directMerFlag) && !"99".equals(directMerFlag.trim())){
			if(!Role.SYSTEM.getCode().equals(supRoleType)){
				if("1".equals(directMerFlag)){
					sb.append(" and exists (select ti.mcht_cd from apsmgm.t_pos_agent pa where ti.device_cd = pa.serial_num and ti.tmnl_brand = pa.pos_brand");
					sb.append(" and pa.zs_flag = 'Y' and pa.agent_id = ").append(oprId).append(")");
				}else{
					sb.append(" and not exists (select ti.mcht_cd from apsmgm.t_pos_agent pa where ti.device_cd = pa.serial_num and ti.tmnl_brand = pa.pos_brand");
					sb.append(" and pa.zs_flag = 'Y' and pa.agent_id = ").append(oprId).append(")");
				}
			}
		}
		return sb.toString();
	}

	@Override
	protected String getSearchHQL() {
		StringBuffer sb = new StringBuffer();
		sb.append("select mi.mcht_cd,mi.name_busi,mi.nation_cd,(SELECT tii.ins_name FROM tbl_ins_inf tii where mi.bank_belong_cd = tii.ins_id_cd) orgName,");
		sb.append("mi.status,mi.contact_name,mi.contact_phoneno,mi.certif,mi.certif_no,b.name,b.account,b.bank_name,b.bank_code,");
			sb.append("(select tp.real_name from t_operators tp where tp.id = (select p1.agent_id from t_pos_agent p1  where p1.serial_num = ti.device_cd and p1.zs_flag='Y' and p1.pos_brand = ti.tmnl_brand)) real_Name,");
			sb.append("(select ta.name from tbl_area ta where ta.id = (select tp.companyid from t_operators tp where tp.id = (select p2.agent_id from t_pos_agent p2  where p2.serial_num = ti.device_cd and p2.pos_brand = ti.tmnl_brand and p2.zs_flag='Y'))) company_name,");
			sb.append("(select p3.agent_id from t_pos_agent p3 where p3.zs_flag = 'Y' and p3.serial_num = ti.device_cd and p3.pos_brand = ti.tmnl_brand) ext_id,mi.appl_date,");
			sb.append("nvl2((select distinct prod_cd from tbl_mcht_biz_deal t where t.mcht_cd = mi.mcht_cd and t.prod_cd = '1000'),'T1',''),");
			sb.append("nvl2((select distinct prod_cd from tbl_mcht_biz_deal t where t.mcht_cd = mi.mcht_cd and t.prod_cd = '1171'),'T0','')");
			sb.append(" from tbl_mcht_inf mi, tbl_mcht_bankaccount b, tbl_term_inf ti");
		if(Role.O_MANAGER.getCode().equals(supRoleType) || Role.AGENT.getCode().equals(supRoleType)){
			sb.append(",(select a.serial_num, a.pos_brand from t_pos_agent a,(select to_char(t.id) tid from t_operators t start with id =").append(supOprId);
			sb.append(" connect by prior t.id = t.supri_id and t.role_type not in ('15', '16', '17')) tt where a.agent_id = tt.tid group by a.serial_num, a.pos_brand) tem ");
		}
		if(Role.MARKET_DIRECT.getCode().equals(roleType) || Role.MARKET_LEAD.getCode().equals(roleType)){
			sb.append(",(select a.serial_num, a.pos_brand from t_pos_agent a,");
			sb.append("(select m.id tid  from t_operators m where m.role_type in ('02', '03') and m.oneagentid in ");
			sb.append("(select agentid from t_oper_role t where t.operid = ").append(oprId).append(")) tt where a.agent_id = tt.tid group by a.serial_num, a.pos_brand) tem ");
		}
		sb.append(" where mi.group_cd='3' and mi.mcht_cd = b.owner_cd and mi.mcht_cd = ti.mcht_cd ");
		if(Role.O_MANAGER.getCode().equals(supRoleType) || Role.AGENT.getCode().equals(supRoleType)
				|| Role.MARKET_DIRECT.getCode().equals(roleType) || Role.MARKET_LEAD.getCode().equals(roleType)){
			sb.append(" and ti.device_cd = tem.serial_num and ti.tmnl_brand = tem.pos_brand");
		}
		if(Constance.isNotEmpty(directMerFlag) && !"99".equals(directMerFlag.trim())){
			if(!Role.SYSTEM.getCode().equals(supRoleType)){
				if("1".equals(directMerFlag)){
					sb.append(" and exists (select ti.mcht_cd from apsmgm.t_pos_agent pa where ti.device_cd = pa.serial_num and ti.tmnl_brand = pa.pos_brand");
					sb.append(" and pa.zs_flag = 'Y' and pa.agent_id = ").append(oprId).append(")");
				}else{
					sb.append(" and not exists (select ti.mcht_cd from apsmgm.t_pos_agent pa where ti.device_cd = pa.serial_num and ti.tmnl_brand = pa.pos_brand");
					sb.append(" and pa.zs_flag = 'Y' and pa.agent_id = ").append(oprId).append(")");
				}
			}
		}
		return sb.toString();
	}

	@Override
	protected String getOrderPartOfHQL() {
		return null;
	}

	public String getRoleType() {
		return roleType;
	}

	public void setRoleType(String roleType) {
		this.roleType = roleType;
	}

	public String getOprId() {
		return oprId;
	}

	public void setOprId(String oprId) {
		this.oprId = oprId;
	}

	public String getMchtCd() {
		return mchtCd;
	}

	public void setMchtCd(String mchtCd) {
		this.mchtCd = mchtCd;
	}

	public String getMchtName() {
		return mchtName;
	}

	public void setMchtName(String mchtName) {
		this.mchtName = mchtName;
	}

	public String getOrgCode() {
		return orgCode;
	}

	public void setOrgCode(String orgCode) {
		this.orgCode = orgCode;
	}

	public String getSupRoleType() {
		return supRoleType;
	}

	public void setSupRoleType(String supRoleType) {
		this.supRoleType = supRoleType;
	}

	public String getSupOprId() {
		return supOprId;
	}

	public void setSupOprId(String supOprId) {
		this.supOprId = supOprId;
	}

	public String getStatus() {
		return status;
	}

	public void setStatus(String status) {
		this.status = status;
	}

	public String getDirectMerFlag() {
		return directMerFlag;
	}

	public void setDirectMerFlag(String directMerFlag) {
		this.directMerFlag = directMerFlag;
	}

	public String getCardNo() {
		return cardNo;
	}

	public void setCardNo(String cardNo) {
		this.cardNo = cardNo;
	}

	public String getRsvl3() {
		return rsvl3;
	}

	public void setRsvl3(String rsvl3) {
		this.rsvl3 = rsvl3;
	}

	public String getPerName() {
		return perName;
	}

	public void setPerName(String perName) {
		this.perName = perName;
	}

	public String getPerPhone() {
		return perPhone;
	}

	public void setPerPhone(String perPhone) {
		this.perPhone = perPhone;
	}

	public String getMinDate() {
		return minDate;
	}

	public void setMinDate(String minDate) {
		this.minDate = minDate;
	}

	public String getMaxDate() {
		return maxDate;
	}

	public void setMaxDate(String maxDate) {
		this.maxDate = maxDate;
	}

}